﻿using System;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Model;

namespace DAL
{
    /// <summary>
    /// 从Excel中导入数据
    /// </summary>
    public class ImportDataFromExcel
    {

        public List<Students_MD> GetStudentsFromExcel(string path)
        {
            List<Students_MD> Students = new List<Students_MD>();
            string sql = "select *from [Students$]";
            try
            {
                OleDbDataReader s = OleDbHelper.GetDataReader(sql, path);
                while (s.Read())
                {
                    if (s["姓名"].ToString() == "")
                    {
                        break;
                    }
                    Students.Add(new Model.Students_MD()
                    {
                        StudentName = s["姓名"].ToString(),
                        Gender =s["性别"].ToString(),
                        Birthday = s["出生日期"].ToString(),
                        StudentIdNo = s["身份证号"].ToString(),
                        PhoneNumber = s["电话号码"].ToString(),
                        StudentAddress = s["家庭住址"].ToString(),
                        ClassId = Convert.ToInt32(s["班级编号"]),
                        Age = SQLHepler.GetDateTime().Year - ((DateTime)s["出生日期"]).Year
                    });
                }
                s.Close();
            }
            catch (Exception ex)
            {
                throw new Exception("Excel 获取数据异常: " + ex.Message);
            }
            return Students;
        }
        public DataSet GetStudentsFromExcel_1(string path)
        {
            string sql = "select *from [Students$]";
            DataSet set = OleDbHelper.GetDataset(sql, path);
            return set;
        }
    }
    
}
